import time
import pymysql.cursors
import numpy as np
import pymysql
import pandas as pd
from joblib import dump

class MysqlUtil(object):
    def __init__(self) -> None:
        self.conn = pymysql.connect(
            host="127.0.0.3",
            user="root",  # 原代码中是name，应该是user
            password="123456",
            db="customer",
            port=3308,
            charset="utf8"
        )

    def is_holiday(self,data):
        """
        """
        if data in ['2024-09-03','2024-10-01','2024-10-02','2024-10-03','2024-10-04','2024-10-06','2024-10-07','2025-01-01','2025-01-02','2025-01-03','2025-01-04','2025-01-05','2025-01-06''2025-01-07']
            return 1
        return 0    



    def get_data(self):
        cursor= self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql= """
        SELECT DATE(g.create_time) as date, HOUR(g.create_time) as hour, count(*) as count 
        FROM order_user_gate_rel g 
        WHERE HOUR(g.create_time) BETWEEN 6 and 23 
        GROUP BY date, hour
        """
       
        cursor.execute(sql)
        ret =cursor.fetchall()
        df =pd.DataFrame(ret)
        date_range = pd.date_range(start='2024-07-01',end='2025-01-01',freq='D')
        hours=range(6,24)
        full_index=pd.MultiIndex.from_product([date_range,hours],names=['date','hours'])
        da_full =df.set_index(['date','hour']).reindex(full_index,fill_value=0).reset_index()
        df_pivot =df_full.pivot(index='date',colums='hour',values='count')
        print(df_pivot.head)
        df_pivot['dow'] =df_pivot.index.dayofweek
        df_pivot['month'] =df_pivot.index.month
        df_pivot['is_holiday'] =df_pivot.index.map(self.is_holiday)
        print(df_pivot.head)

        df_pivot.pd.get_dummises(df_pivot,columns=['dow','month'],dtype =int)


        hours_columns= list(range(6,24))
        df_hours =df_pivot[hours_columns].copy()
        feature_columns = [col for col in df_pivot.columns if col not in hours_columns]
        df_feature = df_pivot[feature_columns].copy()

        scaler = MinMaxScaler()
        scaled_hours = scaler.fit_transform(df_hours)
        dump(scaler,'NN/scenic_joblib')

            # 将同一化后的数据转换为DataFrame
        df_hours_scaled = pd.DataFrame(scaled_hours, columns=hours_columns, index=df_hours.index)

        # 合并
        df_pivot_clean = pd.concat([df_hours_scaled, df_feature], axis=1)
        print(df_pivot_clean.head)
        df_pivot_clean.to_csv('NN/scenic_data.csv',index=False)

if __name__ =="__main__":
    mu=MysqlUtil()
    mn.get_data()